﻿using System;
using System.Collections.Generic;
using System.Text;
using PPI.Entities.Distribution;
using PPI.Mobile.Logic;
using System.Data;
using VMind.Global;

namespace PPI.Client.Logic.Distribution
{
    public class LNote
    {
        public static List<ENote> SelectAllIdName()
        {
            DataTable dt = WebConsumer.Distribution.WDistributionNotes.SelectAllIdName().Tables[0];
            List<ENote> lstNotes = new List<ENote>();

            foreach (DataRow drNote in dt.Rows)
            {
                ENote note = new ENote();
                                                        
                note.Id = ConvertHelper.ToInt32(drNote["Vendor_InspVisit_XmissionStructures_NoteId"]);
                note.Name = drNote["Name"].ToString();

                lstNotes.Add(note);
            }

            return lstNotes;
        }

        public static List<ENote> Save(List<ENote> notes, Int32 poleId)
        {
            return WebConsumer.Distribution.WPole.Save(poleId, notes);
        }

        public static void UpdateUploadedData(List<ENote> notes, Int32 poleId, string connectionString)
        {
            foreach (ENote note in notes)
            {
                StringBuilder _query = new StringBuilder();
                _query.AppendLine("UPDATE tb_PoleResults set PoleResultServerID = " + note.ServerId + ", PoleServerID = " + poleId + ", UploadedDate = " +
                                  DateTime.Now.ToString("yyyy-MM-dd") + " where PoleResult = " + note.Id + " ");

                LMobileDatabase.ExecuteQuery(_query.ToString(), connectionString);
            }
        }

        public static List<ENote> GetPoleNotes(EDistributionPole distributionPole, string connectionString)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   PR.PoleResult, ");
            _sql.AppendLine("   PR.ResultTypeFk, ");
            _sql.AppendLine("   LKS.LookupText ");
            _sql.AppendLine("FROM tb_PoleResults PR ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKS ON LKS.LookupValue = PR.ResultTypeFk AND LKS.LookupTypeFk = 'InspectionResult' ");
            _sql.AppendLine("WHERE PR.PoleFk = " + distributionPole.Id + ";");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<ENote> lstNotes = new List<ENote>();

            foreach (DataRow dr in dt.Rows)
            {
                ENote poleNote = new ENote();

                poleNote.Id = ConvertHelper.ToInt32(dr["PoleResult"]);
                poleNote.Key = dr["ResultTypeFk"].ToString();
                poleNote.Name = dr["LookupText"].ToString();

                lstNotes.Add(poleNote);
            }

            return lstNotes;
        }

        public static List<ENote> GetProjectPoleNotes(EDistributionPole distributionPole, string connectionString)
        {
            StringBuilder _sql = new StringBuilder();

            #region Query

            _sql.AppendLine("SELECT ");
            _sql.AppendLine("   PR.PoleResult, ");
            _sql.AppendLine("   PR.ResultTypeFk, ");
            _sql.AppendLine("   LKS.LookupText ");
            _sql.AppendLine("FROM tb_PoleResults PR ");
            _sql.AppendLine("LEFT JOIN tb_Lookups LKS ON LKS.LookupValue = PR.ResultTypeFk AND LKS.LookupTypeFk = 'InspectionResult' ");
            _sql.AppendLine("WHERE PR.PoleFk = " + distributionPole.Id + ";");

            #endregion

            DataTable dt = LMobileDatabase.ExecuteQueryDataset(_sql.ToString(), connectionString).Tables[0];

            List<ENote> lstNotes = new List<ENote>();

            foreach (DataRow dr in dt.Rows)
            {
                ENote poleNote = new ENote();

                poleNote.Id = ConvertHelper.ToInt32(dr["PoleResult"]);
                poleNote.Key = dr["ResultTypeFk"].ToString();
                poleNote.Name = dr["LookupText"].ToString();

                lstNotes.Add(poleNote);
            }

            return lstNotes;
        }

        public static List<ENote> GetProjectPoleNotes(DataTable dt)
        {
            List<ENote> lstNotes = new List<ENote>();

            foreach (DataRow dr in dt.Rows)
            {
                ENote poleNote = new ENote();

                poleNote.Id = ConvertHelper.ToInt32(dr["PoleResult"]);
                poleNote.Key = dr["ResultTypeFk"].ToString();
                poleNote.Name = dr["LookupText"].ToString();

                lstNotes.Add(poleNote);
            }

            return lstNotes;
        }

    }
}
